package itsm.isperp.module.service.change;

import java.util.List;
import java.util.Map;

import itsm.isperp.framework.data.domain.PageResponse;
import itsm.isperp.framework.service.BaseService;
import itsm.isperp.framework.web.request.JqGridFilterRuleOp;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.module.dto.project.ProjectDomain;
import itsm.isperp.module.dto.project.ProjectDomain.ProjectDomainType;
import itsm.isperp.module.entity.change.ChgChange;
import itsm.isperp.module.repository.change.ChgChangeMapper;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * 
 * 
 * 
 * @author lizx
 * @date 2014-7-18
 * @since 1.0
 * 
 */
@Service
public class ChgChangeService extends BaseService<ChgChange> {

	@Autowired
	protected ChgChangeMapper chgChangeMapper;

	@Autowired
	protected JdbcTemplate jdbcTemplate;

	@Override
	public ChgChangeMapper getRepository() {
		return chgChangeMapper;
	}

	public List<Map<String, Object>> findCiChangeDetail(String ciId) {
		/*
		 * JqGridRequest req = new JqGridRequest(); req.setPageable(false);
		 * req.addFilterRule("ci_id", JqGridFilterRuleOp.eq, ciId);
		 * req.addSidx("create_date DESC"); return
		 * chgChangeMapper.findAllDetailByRequest(req);
		 */
		String sql = "SELECT a.* FROM  chg_change a WHERE EXISTS (SELECT 1 FROM conf_ci_use_relation b WHERE b.relation_id=a.id  AND ci_id='"
				+ ciId + "') order by create_date desc";
		return jdbcTemplate.queryForList(sql);

	}

	public PageResponse<Map<String, Object>> searchAllByFilter(
			JqGridRequest request) {
		request.setProjectDomain(ProjectDomain.newInstance("project_id",
				ProjectDomainType.Technology));
		return chgChangeMapper.findAllDetailByRequest(request).toPage();
		// return chgChangeMapper.searchAllByFilter(request).toPage();
	}

	public List<Map<String, Object>> findCiPropertyPlanChange(String changeId) {
		String sql = "SELECT distinct change_id,columnname,ci_id,columnvalue,columnkey,columnvalue_before,cicode,ciname,cicty FROM("
				+ "SELECT  change_id,ci_id,a.name AS columnname,columnkey,columnvalue,columnvalue_before,d.code AS cicode,d.name AS ciname,d.conf_category_name AS cicty,'0' isex FROM db_entity_field a INNER JOIN chg_change_configuration AS c ON c.columnkey=a.column_name INNER JOIN conf_ci d ON c.ci_id=d.id WHERE EXISTS (SELECT 1 FROM db_entity b WHERE table_name='conf_ci' AND a.db_entity_id=b.id AND a.is_cichange_chfield='1')"
				+ "AND  c.is_category_proprety='0'"
				+ " UNION ALL "
				+ "SELECT k.change_id,k.ci_id,m.name AS columnname,columnkey,columnvalue,columnvalue_before,h.code AS cicode,h.name AS ciname,h.conf_category_name AS cicty,'1' isex  FROM conf_category_property m INNER JOIN conf_ci_property n ON m.id=n.conf_category_property_id INNER JOIN "
				+ "chg_change_configuration k ON k.columnkey=n.conf_category_property_id  INNER JOIN conf_ci h ON h.id=k.ci_id) t where change_id=? ORDER BY cicode,isex";
		return jdbcTemplate.queryForList(sql, changeId);

	}

	public List<Map<String, Object>> findCiInspectDetail(String ciId) {
		String sql = "SELECT a.* ,b.check_name,c.name,c.code,c.act_principal_id,"
				+ " c.act_principal_name,c.create_date,c.act_start_time,c.act_end_time"
				+ " FROM  tsk_inspect_template_ci_check a LEFT JOIN  tsk_inspect_template_check b"
				+ " ON a.template_check_id=b.id LEFT JOIN  tsk_task c ON a.task_id=c.id"
				+ " WHERE a.ci_id='" + ciId + "'  ORDER BY c.create_date DESC";
		return jdbcTemplate.queryForList(sql);

	}
	public Map<String,Object> findEvaluationRelateIcd(String changeId) {
		String sql="select count(1) count_c from chg_element_relation a where from_id='"+changeId+"' and exists(select 1 from icd_incident b where b.id=a.to_id) and to_type='chgRelateIcd'";
		return jdbcTemplate.queryForMap(sql);

	}
	public void updateChangeApproveResult(String id,String result) {
		String sql = "update CHG_CHANGE set APPROVE_RESULT='"+result+"' WHERE id='"+id+"'";
		jdbcTemplate.execute(sql);
	}
	
	public List<Map<String,Object>> getChangeTecService(String tecServiceIds)
	{
		String[] temArr=tecServiceIds.split(",");
		String inStr="";
		for(String s:temArr)
		{
			inStr+="'"+s+"',";
		}
		inStr=inStr.substring(0,inStr.length()-1);
		String sql = "select * from tec_service where id in("+inStr+")";
		return jdbcTemplate.queryForList(sql);
	}
	
	public Map<String, Object> findChangeRelateTaskDetail(String id) {
		String sql = "SELECT a.*,b.name cus_name,b.fullname cus_fullname,b.email cus_email,b.mobile cus_mobile,'' cus_mobile_short,b.tel cus_tel,b.user_type cus_type,b.`department_treenode_name_path` cus_dep,b.position cus_position FROM CHG_CHANGE a LEFT JOIN v_app_department_user b ON a.requester=b.name" + " where a.id='" + id + "'";
		return jdbcTemplate.queryForMap(sql);
	}

}
